﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MySQLStudio.BusinessLogic
{
    public class InformationSchemaHelper
    {
        public const string INFORMATION_SCHEMA_TABLE = "INFORMATION_SCHEMA";

        public static string GetDatabasesCommand()
        {
            return "SELECT * FROM INFORMATION_SCHEMA.SCHEMATA";
        }

        public static string GetDatabaseCommand()
        {
            return "SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?schemaName";
        }

        public static string GetTablesInDatabaseCommand()
        {
            return string.Format("SELECT * FROM TABLES WHERE TABLE_SCHEMA = ?databaseName AND TABLE_TYPE <> 'VIEW'");
        }

        public static string GetStoredProceduresDatabaseCommand(string databaseName)
        {
            return string.Format("SELECT * FROM ROUTINES WHERE ROUTINE_SCHEMA = '{0}'", databaseName);
        }

        public static string GetStoredProcedureCreateStatementCommand(string storedProcedureName, string databaseName)
        {
            return string.Format("SHOW CREATE PROCEDURE `{0}`.`{1}`", databaseName, storedProcedureName);
        }

        public static string GetStoreProcedureAlterStatementCommand(string storedProcedureName, string databaseName)
        {
            return string.Format("SHOW CREATE PROCEDURE `{0}`.`{1}`", databaseName, storedProcedureName);
        }

        public static string GetCreateStoredProcedureStatementCommand()
        {
            return "CREATE PROCEDURE `test`.`new_routine` () \n  BEGIN\n  END\n";
        }

        public static string GetDeleteProcedureStatementCommand(string storeProcedureName, string databaseName)
        {
            return string.Format("DROP PROCEDURE `{0}`.`{1}`", databaseName, storeProcedureName);
        }

        /// <summary>
        /// View state commands
        /// </summary>
        /// <param name="viewName"></param>
        /// <returns></returns>
        public static string GetViewDatabaseCommand(string databaseName)
        {
            return String.Format("SELECT * FROM `information_schema`.`VIEWS` where TABLE_SCHEMA = '{0}'", databaseName);
        }

        public static String GetCreateViewStatementCommand(String schemaName, String viewName)
        {
            return string.Format("SHOW CREATE VIEW `{0}`.`{1}`", schemaName, viewName);
        }

        public static string GetViewAlterStatementCommand(String viewName)
        {
            return string.Format("ALTER VIEW {0}", viewName);
        }

        public static string GetViewDropStatementCommand(String viewName)
        {
            return string.Format("DROP VIEW {0}", viewName);
        }

        public static string GetViewColumnsDataBaseCommand(String schemaName, String viewName)
        {
            return string.Format("select * from `information_schema`.`COLUMNS` where TABLE_SCHEMA = '{0}' and TABLE_NAME = '{1}'", schemaName, viewName);
        }

        public static string GetTableColumnsCommand()
        {
            return string.Format("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ?databaseName and TABLE_NAME = ?tableName");
        }

        public static string GetCollationsCommand()
        {
            return "SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS ORDER BY COLLATION_NAME";
        }

        public static string GetRenameTableCommand()
        {
            return "ALTER TABLE `{0}`.`{1}` RENAME `{0}`.`{2}`";
        }

        public static string GetDropTableCommand()
        {
            return "DROP TABLE `{0}`.`{1}`";
        }

        public static string GetAlterTableColumnCommand()
        {
            return "ALTER TABLE `{0}`.`{1}` CHANGE {2};";
        }

        public static string GetAddTableColumnCommand()
        {
            return "ALTER TABLE `{0}`.`{1}` ADD {2};";
        }

        public static string GetDropTableColumnCommand()
        {
            return "ALTER TABLE `{0}`.`{1}` DROP `{2}`;";
        }

        public static string GetCreateTableCommand()
        {
            return "CREATE  TABLE IF NOT EXISTS `{0}`.`{1}` ( {2}{3} );";
        }

        public static string GetCreatePrimaryKeyCommand()
        {
            return ", PRIMARY KEY ({0})";
        }

        public static string GetCreateUniqueKeyCommand()
        {
            return "UNIQUE INDEX `{0}_UNIQUE` (`{0}`)";
        }

        public static string GetTransactionCommand()
        {
            return "\nSTART TRANSACTION\nDECLARE EXIT HANDLER\n\tFOR SQLEXCEPTION, SQLWARNING, NOT FOUND\n\t\tROLLBACK;\n{0}\nCOMMIT;\n";
        }


        public static string GetDropDatabseCommand(string databaseName)
        {
            return string.Format("\nDROP DATABASE {0};", databaseName);
        }

        public static string GetCreateDatabaseCommand()
        {
            return "\nCREATE DATABASE db_name;";
        }

        //SELECT * FROM `information_schema`.`VIEWS`;
        //public static string GetViewDatabaseCommand(String databaseName)
        //{
        //    return string.Format("select * from views where view_schema = '{0}'", databaseName);
        //}
        // return string.Format("CREATE VIEW");
        //==================================
        // end view functions
        //==================================
    }
}
